﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace KioskosJugandoLimpio.Classes
{
    public class SQL
    {
        private static string connStr = "Data Source=localhost; Initial Catalog=kioskos; Integrated Security =SSPI;";
        public static SqlTransaction fnGetTran() {
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();
           
            return tran;
        }
        public static void fnCommit(SqlTransaction tran)
        {
            tran.Commit();
            tran.Connection.Close();
            tran.Dispose();
        }
        public static void fnRollback(SqlTransaction tran)
        {
            if (tran != null)
            {
                tran.Rollback();
                if (tran.Connection.State == ConnectionState.Open)
                    tran.Connection.Close();
                tran.Dispose();
            }
        }
        public static DataSet fnDataSet(string query)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using(SqlDataAdapter da = new SqlDataAdapter(query , conn)){
                    da.Fill(ds);
                }
            }
            return ds;
        }
        public static DataTable fnDataTable(string query) {
            return fnDataSet(query).Tables[0];
        }

        public static object fnEscalar(string query)
        {
            return fnDataTable(query).Rows[0][0];
        }


        internal static int fnExec(string query)
        {
            int retVal;
            if (int.TryParse(fnEscalar(query + "; SELECT @@IDENTITY as A").ToString(), out retVal))
            {
                return retVal;
            }
            return int.MinValue;
        }
        internal static int fnExec(string query , SqlTransaction tran)
        {
            int retVal;
            if (int.TryParse(fnEscalar(query + "; SELECT @@IDENTITY as A", tran).ToString(), out retVal))
            {
                return retVal;
            }
            return int.MinValue;
        }

        private static object fnEscalar(string query, SqlTransaction tran)
        {
            return fnDataTable(query, tran).Rows[0][0];
        }

        private static DataTable fnDataTable(string query, SqlTransaction tran)
        {
            return fnDataSet(query, tran).Tables[0];
        }

        private static DataSet fnDataSet(string query, SqlTransaction tran)
        {
            DataSet ds = new DataSet();
            SqlCommand command = new SqlCommand(query, tran.Connection, tran);
            using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    da.Fill(ds);
                }
            return ds;
        }
    }
}